CREATE VIEW vw_container
AS
SELECT     Invoice_GDN.Inv_GDNID, Invoice_GDN.Inv_GDNDate, Invoice_GDN.Inv_GDNVNo, Invoice_GDN.BLNo, Invoice_GDNitem.Dis_Date, Invoice_GDNitem.PartyID, 
                      Accounts.AccountTitle AS PartyName, Invoice_GDNitem.ItemID, vw_Items.ItemName, Invoice_GDNitem.VarietyID, vw_Items.VarietyName, Invoice_GDNitem.Qty, 
                      Invoice_GDNitem.ContainerNo, Invoice_GDNitem.DRemarks
FROM         Invoice_GDN INNER JOIN
                      Invoice_GDNitem ON Invoice_GDN.Inv_GDNID = Invoice_GDNitem.Inv_GDNID LEFT OUTER JOIN
                      vw_Items ON Invoice_GDNitem.VarietyID = vw_Items.VarietyID LEFT OUTER JOIN
                      Accounts ON Invoice_GDNitem.PartyID = Accounts.AccountNo
UNION ALL
SELECT     Invoice_GRN.Inv_GRNID, Invoice_GRN.Inv_GRNDate, Invoice_GRN.Inv_GRNVNo, Invoice_GRN.BENo, Invoice_GRN.BEDate, Invoice_GRN.SupplierID AS PartyID, 
                      Accounts.AccountTitle AS PartyName, Invoice_GRNItem.ItemID, vw_Items.ItemName, Invoice_GRNItem.VarietyID, vw_Items.VarietyName, Invoice_GRNItem.LQTY, 
                      Invoice_GRNItem.ContainerNo, Invoice_GRN.Remarks
FROM         Invoice_GRNItem INNER JOIN
                      Invoice_GRN ON Invoice_GRNItem.inv_GRNID = Invoice_GRN.Inv_GRNID LEFT OUTER JOIN
                      Accounts ON Invoice_GRN.SupplierID = Accounts.AccountNo LEFT OUTER JOIN
                      vw_Items ON Invoice_GRNItem.VarietyID = vw_Items.VarietyID


--------------------------------------------------------

CREATE TABLE [dbo].[Invoice_SSP] (
	[Inv_SSPID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_SSPDate] [datetime] NOT NULL ,
	[Inv_SSPVNo] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SessionID] [smallint] NOT NULL ,
	[Remarks] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReadOnly] [bit] NOT NULL ,
	[LoginID] [int] NOT NULL ,
	[HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EntryDateTime] [datetime] NULL ,
	[ModifyID] [int] NULL ,
	[ModifyHostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModifyDateTime] [datetime] NULL ,
	[BEDate] [datetime] NULL ,
	[BENo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Currency] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ContainerNo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NoOfContainers] [numeric](15, 0) NOT NULL ,
	[SumLQTY] [numeric](15, 4) NOT NULL ,
	[SumSQTY] [numeric](15, 4) NOT NULL ,
	[SumDQTY] [numeric](15, 4) NOT NULL ,
	[SumTQTY] [numeric](15, 4) NOT NULL ,
	[SumLQTY2] [numeric](15, 4) NOT NULL ,
	[SumGross] [numeric](15, 4) NOT NULL ,
	[SumFreight] [numeric](15, 4) NOT NULL ,
	[SumOtCharges] [numeric](15, 4) NOT NULL ,
	[SumComission] [numeric](15, 4) NOT NULL ,
	[SumNetSale] [numeric](15, 4) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice_SSParty] (
	[inv_SSPartyID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[inv_SSPID] [bigint] NOT NULL ,
	[DebitID] [int] NULL ,
	[SubID] [int] NULL ,
	[ItemID] [smallint] NULL ,
	[VarietyID] [smallint] NULL ,
	[SQTY] [numeric](15, 4) NOT NULL ,
	[DQTY] [numeric](15, 4) NOT NULL ,
	[TQTY] [numeric](15, 4) NOT NULL ,
	[LQTY] [numeric](15, 4) NOT NULL ,
	[Gross] [numeric](15, 4) NOT NULL ,
	[Freight] [numeric](15, 4) NOT NULL ,
	[OtCharges] [numeric](15, 4) NOT NULL ,
	[Comission] [numeric](15, 4) NOT NULL ,
	[NetSale] [numeric](15, 4) NOT NULL ,
	[StatusID] [smallint] NULL ,
	[Type] [tinyint] NOT NULL ,
	[Remarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContainerNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RefVNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SaleDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice_SSPitem] (
	[inv_SSPItemID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[inv_SSPID] [bigint] NOT NULL ,
	[StationID] [smallint] NULL ,
	[ItemID] [smallint] NULL ,
	[VarietyID] [smallint] NULL ,
	[LQTY] [numeric](15, 4) NOT NULL ,
	[StatusID] [smallint] NULL ,
	[Type] [tinyint] NOT NULL ,
	[ContainerNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RefVNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoice_SSP] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_SSP] PRIMARY KEY  CLUSTERED 
	(
		[Inv_SSPID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_SSParty] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_SSParty] PRIMARY KEY  CLUSTERED 
	(
		[inv_SSPartyID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_SSPitem] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_SSPitem] PRIMARY KEY  CLUSTERED 
	(
		[inv_SSPItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_SSP] ADD 
	CONSTRAINT [DF_Invoice_SSP_ReadOnly] DEFAULT (0) FOR [ReadOnly],
	CONSTRAINT [DF_Invoice_SSP_HostName] DEFAULT (host_name()) FOR [HostName],
	CONSTRAINT [DF_Invoice_SSP_EntryDateTime] DEFAULT (getdate()) FOR [EntryDateTime],
	CONSTRAINT [DF_Invoice_SSP_NoOfContainers] DEFAULT (0) FOR [NoOfContainers],
	CONSTRAINT [DF_Invoice_SSP_SumLQTY] DEFAULT (0) FOR [SumLQTY],
	CONSTRAINT [DF_Invoice_SSP_SumSQTY] DEFAULT (0) FOR [SumSQTY],
	CONSTRAINT [DF_Invoice_SSP_SumDQTY] DEFAULT (0) FOR [SumDQTY],
	CONSTRAINT [DF_Invoice_SSP_SumTQTY] DEFAULT (0) FOR [SumTQTY],
	CONSTRAINT [DF_Invoice_SSP_SumLQTY2] DEFAULT (0) FOR [SumLQTY2],
	CONSTRAINT [DF_Invoice_SSP_SumGross] DEFAULT (0) FOR [SumGross],
	CONSTRAINT [DF_Invoice_SSP_SumFreight] DEFAULT (0) FOR [SumFreight],
	CONSTRAINT [DF_Invoice_SSP_SumOtCharges] DEFAULT (0) FOR [SumOtCharges],
	CONSTRAINT [DF_Invoice_SSP_SumComission] DEFAULT (0) FOR [SumComission],
	CONSTRAINT [DF_Invoice_SSP_SumNetSale] DEFAULT (0) FOR [SumNetSale]
GO

ALTER TABLE [dbo].[Invoice_SSParty] ADD 
	CONSTRAINT [DF_Invoice_SSParty_SQTY] DEFAULT (0) FOR [SQTY],
	CONSTRAINT [DF_Invoice_SSParty_DQTY] DEFAULT (0) FOR [DQTY],
	CONSTRAINT [DF_Invoice_SSParty_TQTY] DEFAULT (0) FOR [TQTY],
	CONSTRAINT [DF_Invoice_SSParty_LQTY] DEFAULT (0) FOR [LQTY],
	CONSTRAINT [DF_Invoice_SSParty_Gross] DEFAULT (0) FOR [Gross],
	CONSTRAINT [DF_Invoice_SSParty_Freight] DEFAULT (0) FOR [Freight],
	CONSTRAINT [DF_Invoice_SSParty_OtCharges] DEFAULT (0) FOR [OtCharges],
	CONSTRAINT [DF_Invoice_SSParty_Comission] DEFAULT (0) FOR [Comission],
	CONSTRAINT [DF_Invoice_SSParty_NetSale] DEFAULT (0) FOR [NetSale],
	CONSTRAINT [DF_Invoice_SSParty_Type] DEFAULT (0) FOR [Type]
GO

ALTER TABLE [dbo].[Invoice_SSPitem] ADD 
	CONSTRAINT [DF_Invoice_SSPitem_LQTY] DEFAULT (0) FOR [LQTY],
	CONSTRAINT [DF_Invoice_SSPitem_Type] DEFAULT (0) FOR [Type]
GO